if object_id('BIMonitor.SystemHealthReport') is not null
  drop procedure BIMonitor.SystemHealthReport
go

create procedure BIMonitor.SystemHealthReport

  @Environment varchar(75)

as

select
  ComponentName as Component,
  ComponentType,
  ServerName,
  case
    when ComponentState = '' then 'Unknown'
    when ComponentName = 'Log Drive % Disk Free' then ComponentState + '%'
    when ComponentName = 'Data Drive Free MB' then ComponentState + ' MB'
    when ComponentName = 'Operating System Free Memory' then ComponentState + ' MB'
    when ComponentName = 'Total Processor Utilization %' then ComponentState + '%'
    else ComponentState
  end ComponentState,
  convert(nvarchar(30),CheckDate,100) CheckDate,
  case
    when ComponentType = 'Service' then 1
    when ComponentType = 'Disk Drive' then 2
    when ComponentType = 'Memory' then 3
    when ComponentType = 'Processor' then 4
    else 5
  end as ComponentTypeSortOrder,
  case
    when ComponentState = '' then 'Black'
    when ComponentType = 'Service' and ComponentState = 'Running' then 'LimeGreen'
    when ComponentType = 'Service' and ComponentState <> 'Running' then 'Red'
    when ComponentName = 'Total Processor Utilization %' and cast(ComponentState as int) >= 90 then 'Red'
    when ComponentName = 'Total Processor Utilization %' and cast(ComponentState as int) < 90 and cast(ComponentState as int) >= 60 then 'Yellow'
    when ComponentName = 'Total Processor Utilization %' and cast(ComponentState as int) < 60 then 'LimeGreen'
    when ComponentName = 'Data Drive Free MB' and cast(ComponentState as int) >= 10000 then 'LimeGreen'
    when ComponentName = 'Data Drive Free MB' and cast(ComponentState as int) < 10000 and cast(ComponentState as int) >= 250 then 'Yellow'
    when ComponentName = 'Data Drive Free MB' and cast(ComponentState as int) < 250 then 'Red'
    when ComponentName = 'Operating System Free Memory' and cast(ComponentState as int) >= 1000 then 'LimeGreen'
    when ComponentName = 'Operating System Free Memory' and cast(ComponentState as int) < 1000 and cast(ComponentState as int) >= 500 then 'Yellow'
    when ComponentName = 'Operating System Free Memory' and cast(ComponentState as int) < 500 then 'Red'
    when ComponentName = 'Log Drive % Disk Free' and cast(ComponentState as int) >= 25 then 'LimeGreen'
    when ComponentName = 'Log Drive % Disk Free' and cast(ComponentState as int) < 25 and cast(ComponentState as int) >= 10 then 'Yellow'
    when ComponentName = 'Log Drive % Disk Free' and cast(ComponentState as int) < 10 then 'Red'
    else 'LightGrey'
  end as SSRSBackgroundColor,
  case
    when ComponentState = '' then 'White'
    when ComponentType = 'Service' and ComponentState = 'Running' then 'White'
    when ComponentType = 'Service' and ComponentState <> 'Running' then 'White'
    when ComponentName = 'Total Processor Utilization %' and cast(ComponentState as int) >= 90 then 'White'
    when ComponentName = 'Total Processor Utilization %' and cast(ComponentState as int) < 90 and cast(ComponentState as int) >= 60 then 'Black'
    when ComponentName = 'Total Processor Utilization %' and cast(ComponentState as int) < 60 then 'White'
    when ComponentName = 'Data Drive Free MB' and cast(ComponentState as int) >= 10000 then 'White'
    when ComponentName = 'Data Drive Free MB' and cast(ComponentState as int) < 10000 and cast(ComponentState as int) >= 250 then 'Black'
    when ComponentName = 'Data Drive Free MB' and cast(ComponentState as int) < 250 then 'White'
    when ComponentName = 'Operating System Free Memory' and cast(ComponentState as int) >= 1000 then 'White'
    when ComponentName = 'Operating System Free Memory' and cast(ComponentState as int) < 1000 and cast(ComponentState as int) >= 500 then 'Black'
    when ComponentName = 'Operating System Free Memory' and cast(ComponentState as int) < 500 then 'White'
    when ComponentName = 'Log Drive % Disk Free' and cast(ComponentState as int) >= 25 then 'White'
    when ComponentName = 'Log Drive % Disk Free' and cast(ComponentState as int) < 25 and cast(ComponentState as int) >= 10 then 'Black'
    when ComponentName = 'Log Drive % Disk Free' and cast(ComponentState as int) < 10 then 'White'
    else 'Red'
  end as SSRSFontColor
from
  BIMonitor.SystemHealth
where
  Environment = @Environment